package cn.example.converter;

import com.alibaba.druid.sql.ast.SQLCurrentTimeExpr;
import com.alibaba.druid.sql.ast.SQLDataTypeImpl;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLIndexDefinition;
import com.alibaba.druid.sql.ast.SQLLimit;
import com.alibaba.druid.sql.ast.SQLObject;
import com.alibaba.druid.sql.ast.SQLOrderBy;
import com.alibaba.druid.sql.ast.SQLOrderingSpecification;
import com.alibaba.druid.sql.ast.expr.SQLAggregateExpr;
import com.alibaba.druid.sql.ast.expr.SQLBinaryExpr;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOperator;
import com.alibaba.druid.sql.ast.expr.SQLCharExpr;
import com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr;
import com.alibaba.druid.sql.ast.expr.SQLInListExpr;
import com.alibaba.druid.sql.ast.expr.SQLIntegerExpr;
import com.alibaba.druid.sql.ast.expr.SQLMethodInvokeExpr;
import com.alibaba.druid.sql.ast.statement.SQLAlterTableAddColumn;
import com.alibaba.druid.sql.ast.statement.SQLAlterTableAddConstraint;
import com.alibaba.druid.sql.ast.statement.SQLAlterTableAddIndex;
import com.alibaba.druid.sql.ast.statement.SQLAlterTableStatement;
import com.alibaba.druid.sql.ast.statement.SQLAssignItem;
import com.alibaba.druid.sql.ast.statement.SQLColumnConstraint;
import com.alibaba.druid.sql.ast.statement.SQLColumnDefinition;
import com.alibaba.druid.sql.ast.statement.SQLConstraint;
import com.alibaba.druid.sql.ast.statement.SQLCreateIndexStatement;
import com.alibaba.druid.sql.ast.statement.SQLDropTableStatement;
import com.alibaba.druid.sql.ast.statement.SQLExprStatement;
import com.alibaba.druid.sql.ast.statement.SQLExprTableSource;
import com.alibaba.druid.sql.ast.statement.SQLJoinTableSource;
import com.alibaba.druid.sql.ast.statement.SQLNotNullConstraint;
import com.alibaba.druid.sql.ast.statement.SQLSelect;
import com.alibaba.druid.sql.ast.statement.SQLSelectItem;
import com.alibaba.druid.sql.ast.statement.SQLSelectOrderByItem;
import com.alibaba.druid.sql.ast.statement.SQLSelectQuery;
import com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource;
import com.alibaba.druid.sql.ast.statement.SQLTableElement;
import com.alibaba.druid.sql.ast.statement.SQLTableSource;
import com.alibaba.druid.sql.ast.statement.SQLTruncateStatement;
import com.alibaba.druid.sql.ast.statement.SQLUnionQuery;
import com.alibaba.druid.sql.dialect.mysql.ast.MySqlKey;
import com.alibaba.druid.sql.dialect.mysql.ast.MySqlPrimaryKey;
import com.alibaba.druid.sql.dialect.mysql.ast.MySqlUnique;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlAlterTableChangeColumn;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlAlterTableModifyColumn;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlCreateTableStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlDeleteStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlInsertStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlRenameTableStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlUpdateStatement;
import com.alibaba.druid.sql.visitor.SQLASTVisitor;
import com.alibaba.druid.sql.visitor.SQLASTVisitorAdapter;
import com.alibaba.druid.util.JdbcConstants;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * CongKe
 *
 * @Date: 2025/5/15 16:17
 * @Description: GBaseConverter
 */
public class GBaseConverter extends BaseConverter {

    public GBaseConverter() {
        super();
    }

    private String schemaName = "public";

    /**
     * 定义MySQL和南大通用数据库的函数映射
     */
    private static final Map<String, String> MYSQL_TO_GBASE_FUNCTION_MAP = new HashMap<>();

    static {
        MYSQL_TO_GBASE_FUNCTION_MAP.put("NOW", "CURRENT_TIMESTAMP");
        MYSQL_TO_GBASE_FUNCTION_MAP.put("IFNULL", "COALESCE");
        MYSQL_TO_GBASE_FUNCTION_MAP.put("GROUP_CONCAT", "STRING_AGG");
        MYSQL_TO_GBASE_FUNCTION_MAP.put("FIND_IN_SET", "STRPOS");
        MYSQL_TO_GBASE_FUNCTION_MAP.put("DATE_FORMAT", "TO_CHAR");
        MYSQL_TO_GBASE_FUNCTION_MAP.put("UNIX_TIMESTAMP", "EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)");
    }


    private String sequenceName = null;



    /**
     * 转换器适配
     *
     * @param dbType 数据库类型
     * @return 是否适配
     */
    @Override
    public boolean support(String dbType) {
        return JdbcConstants.GBASE.name().equals(dbType);
    }




    @Override
    public void convertQueryStatement(SQLSelectStatement statement) {
        SQLSelect select = statement.getSelect();
        if (select != null) {
            SQLSelectQuery query = select.getQuery();
            if (query != null) {
                String convertedSql = processSelectQuery(query);
                if (StringUtils.isNotBlank(convertedSql)) {
                    selectList.add(convertedSql);
                }
            }
        }
    }

    private String processSelectQuery(SQLSelectQuery query) {
        StringBuilder sb = new StringBuilder();
        if (query instanceof SQLSelectQueryBlock) {
            SQLSelectQueryBlock block = (SQLSelectQueryBlock) query;
            // SELECT 列
            sb.append("SELECT ");
            List<SQLSelectItem> selectItems = block.getSelectList();
            for (int i = 0; i < selectItems.size(); i++) {
                if (i > 0) {
                    sb.append(", ");
                }
                sb.append(processSelectItem(selectItems.get(i)));
            }
            // FROM 子句
            SQLTableSource from = block.getFrom();
            if (from != null) {
                sb.append(" FROM ").append(processTableSource(from));
            }

            // WHERE 条件
            SQLExpr where = block.getWhere();
            if (where != null) {
                sb.append(" WHERE ").append(processWhereCondition(where));
            }

            // GROUP BY
            collectSelectAliases(block);
            List<SQLExpr> groupBy = block.getGroupBy() != null ? block.getGroupBy().getItems() : Collections.emptyList();
            if (!groupBy.isEmpty()) {
                sb.append(" GROUP BY ");
                for (int i = 0; i < groupBy.size(); i++) {
                    if (i > 0) {
                        sb.append(", ");
                    }
                    sb.append(groupBy.get(i).toString());
                }
                SQLExpr having = block.getGroupBy().getHaving();
                if (having != null) {
                    SQLExpr replacedHaving = replaceAliasWithExpr(having);
                    sb.append(" HAVING ").append(replacedHaving.toString());
                }
            }
            // ORDER BY
            SQLOrderBy orderBy = block.getOrderBy();
            if (orderBy != null) {
                List<SQLSelectOrderByItem> orderByItems = orderBy.getItems();
                if (orderByItems != null && !orderByItems.isEmpty()) {
                    sb.append(" ORDER BY ");
                    for (int i = 0; i < orderByItems.size(); i++) {
                        SQLSelectOrderByItem item = orderByItems.get(i);
                        if (i > 0) {
                            sb.append(", ");
                        }
                        sb.append(item.getExpr().toString());
                        if (item.getType() == SQLOrderingSpecification.DESC) {
                            sb.append(" DESC");
                        } else {
                            sb.append(" ASC");
                        }
                    }
                }
            }

            // LIMIT / OFFSET 分页
            SQLLimit limit = block.getLimit();
            if (limit != null) {
                sb.append(" ").append(convertPagination(limit.toString()));
            }

        } else if (query instanceof SQLUnionQuery) {
            SQLUnionQuery unionQuery = (SQLUnionQuery) query;
            sb.append("(").append(processSelectQuery(unionQuery.getLeft())).append(")");
            sb.append(" ").append(unionQuery.getOperator()).append(" ");
            sb.append("(").append(processSelectQuery(unionQuery.getRight())).append(")");
        }

        return sb.toString();
    }

    private void collectSelectAliases(SQLSelectQueryBlock block) {
        selectAliasMap.clear();
        for (SQLSelectItem item : block.getSelectList()) {
            if (item.getAlias() != null) {
                selectAliasMap.put(item.getAlias(), item.getExpr());
            }
        }
    }

    private SQLExpr replaceAliasWithExpr(SQLExpr expr) {
        if (expr instanceof SQLIdentifierExpr) {
            String aliasName = ((SQLIdentifierExpr) expr).getName();
            return selectAliasMap.getOrDefault(aliasName, expr);
        } else if (expr instanceof SQLBinaryOpExpr) {
            SQLBinaryOpExpr binaryExpr = (SQLBinaryOpExpr) expr;
            SQLExpr left = replaceAliasWithExpr(binaryExpr.getLeft());
            SQLExpr right = replaceAliasWithExpr(binaryExpr.getRight());
            return new SQLBinaryOpExpr(left, binaryExpr.getOperator(), right);
        }
        // 可继续扩展其他类型...
        return expr;
    }

    private String processSelectItem(SQLSelectItem item) {
        item.getExpr().accept(createFunctionReplaceVisitor());
        String expr = item.getExpr().toString();
        if (item.getAlias() != null) {
            return expr + " AS " + item.getAlias();
        }
        return expr;
    }

    private String processTableSource(SQLTableSource source) {
        if (source instanceof SQLExprTableSource) {
            SQLIdentifierExpr expr = (SQLIdentifierExpr) ((SQLExprTableSource) source).getExpr();
            return expr.getName().replace("`", "");
        } else if (source instanceof SQLJoinTableSource) {
            SQLJoinTableSource join = (SQLJoinTableSource) source;
            String left = processTableSource(join.getLeft());
            String right = processTableSource(join.getRight());
            String on = join.getCondition() != null ? " ON " + join.getCondition().toString() : "";
            return left + " " + join.getJoinType().nameLCase + " " + right + on;
        } else if (source instanceof SQLSubqueryTableSource) {
            SQLSelect subQuery = ((SQLSubqueryTableSource) source).getSelect();
            String alias = ((SQLSubqueryTableSource) source).getAlias();
            return "(" + processSelectQuery(subQuery.getQuery()) + ") AS " + alias;
        }
        return source.toString();
    }

    private String processWhereCondition(SQLExpr where) {
        // 使用 AST 遍历方式替换函数名
        where.accept(createFunctionReplaceVisitor());
        return ((SQLSelectQueryBlock) where.getParent()).getWhere().toString();
    }

    private SQLASTVisitor createFunctionReplaceVisitor() {
        return new SQLASTVisitorAdapter() {
            @Override
            public boolean visit(SQLMethodInvokeExpr expr) {
                handleFunction(expr);
                return true;
            }

            @Override
            public boolean visit(SQLAggregateExpr expr) {
                handleFunction(expr);
                return true;
            }

            private void handleFunction(SQLMethodInvokeExpr expr) {
                String funcName = expr.getMethodName();
                System.out.println("funcName: " + funcName);
                String gbaseFunc = MYSQL_TO_GBASE_FUNCTION_MAP.get(funcName.toUpperCase());
                String gbaseFunc2 = gbaseFunc == null ? MYSQL_TO_GBASE_FUNCTION_MAP.get(funcName.toUpperCase() + "()") : gbaseFunc;
                if (gbaseFunc2 != null) {
                    // 特殊处理 GROUP_CONCAT -> STRING_AGG 需要加参数
                    if ("GROUP_CONCAT".equalsIgnoreCase(funcName)) {
                        handleGroupConcat(expr, gbaseFunc2);
                    } else if ("FIND_IN_SET".equalsIgnoreCase(funcName)) {
                        // FIND_IN_SET(a, b) => STRPOS(b, a) > 0
                        List<SQLExpr> parameters = expr.getParameters();
                        if (parameters.size() >= 2) {
                            SQLExpr a = parameters.get(0);  // 'apple'
                            SQLExpr b = parameters.get(1);  // fruits
                            // 构建 STRPOS(b, a)
                            SQLMethodInvokeExpr strpos = new SQLMethodInvokeExpr("STRPOS");
                            strpos.addParameter(b);
                            strpos.addParameter(a);
                            // 构建 STRPOS(...) > 0
                            SQLBinaryOpExpr gtZero = new SQLBinaryOpExpr(strpos, SQLBinaryOperator.GreaterThan, new SQLIntegerExpr(0));
                            SQLObject parent = expr.getParent();
                            if (parent instanceof SQLBinaryOpExpr) {
                                ((SQLBinaryOpExpr) parent).replace(expr, gtZero);
                            } else if (parent instanceof SQLSelectItem) {
                                ((SQLSelectItem) parent).setExpr(gtZero);
                            } else if (parent instanceof SQLExprStatement) {
                                ((SQLExprStatement) parent).setExpr(gtZero);
                            } else if (parent instanceof SQLSelectQueryBlock) {
                                //  expr.setUsing(gtZero);
                                //expr.replace(expr, gtZero);
                                ((SQLSelectQueryBlock) parent).replace(expr, gtZero);
                            } else if (parent instanceof SQLInListExpr) {
                                // 处理 FIND_IN_SET 在 IN 子句中的情况
                                SQLInListExpr inListExpr = (SQLInListExpr) parent;
                                inListExpr.addTarget(gtZero);
                            } else {
                                // 其他复杂结构需要单独处理
                                System.err.println("Unsupported parent type: " + parent.getClass());
                            }
                        }
                    } else {
                        String funcName2 = gbaseFunc2.replace("()", "");
                        expr.setMethodName(funcName2);
                    }
                }
            }

            private void handleGroupConcat(SQLMethodInvokeExpr expr, String gbaseFunc2) {
                if (expr instanceof SQLAggregateExpr) {
                    SQLAggregateExpr aggExpr = (SQLAggregateExpr) expr;
                    List<SQLExpr> parameters = aggExpr.getArguments();

                    if (!parameters.isEmpty()) {
                        SQLExpr col = parameters.get(0);
                        SQLCharExpr defaultSep = new SQLCharExpr(",");
                        SQLMethodInvokeExpr stringAgg = new SQLMethodInvokeExpr("STRING_AGG"); // STRING_AGG
                        stringAgg.addParameter(col);
                        stringAgg.addParameter(defaultSep);

                        SQLObject parent = expr.getParent();
                        if (parent instanceof SQLBinaryOpExpr) {
                            ((SQLBinaryOpExpr) parent).replace(expr, stringAgg);
                        } else if (parent instanceof SQLSelectItem) {
                            ((SQLSelectItem) parent).setExpr(stringAgg);
                        }
                    }
                } else if (expr instanceof SQLMethodInvokeExpr) {
                    // 处理 GROUP_CONCAT 作为普通函数的情况（如果有的话）
                    SQLMethodInvokeExpr methodExpr = (SQLMethodInvokeExpr) expr;
                    List<SQLExpr> parameters = methodExpr.getParameters();
                    if (parameters.size() == 1) {
                        SQLExpr col = parameters.get(0);
                        SQLCharExpr defaultSep = new SQLCharExpr(",");
                        SQLMethodInvokeExpr stringAgg = new SQLMethodInvokeExpr("STRING_AGG");
                        stringAgg.addParameter(col);
                        stringAgg.addParameter(defaultSep);
                        SQLObject parent = expr.getParent();
                        if (parent instanceof SQLBinaryOpExpr) {
                            ((SQLBinaryOpExpr) parent).replace(expr, stringAgg);
                        } else if (parent instanceof SQLSelectItem) {
                            ((SQLSelectItem) parent).setExpr(stringAgg);
                        }
                    }
                }
            }
        };
    }

    private static String convertPagination(String query) {
        Pattern pattern1 = Pattern.compile("LIMIT\\s+(\\d+)\\s*,\\s*(\\d+)", Pattern.CASE_INSENSITIVE);
        Matcher matcher1 = pattern1.matcher(query);
        if (matcher1.find()) {
            String offset = matcher1.group(1);
            String limit = matcher1.group(2);
            return matcher1.replaceFirst("LIMIT " + limit + " OFFSET " + offset);
        }

        Pattern pattern2 = Pattern.compile("LIMIT\\s+(\\d+)\\s+OFFSET\\s+(\\d+)", Pattern.CASE_INSENSITIVE);
        Matcher matcher2 = pattern2.matcher(query);
        if (matcher2.find()) {
            return matcher2.group(0); // 已经是 GBase 格式
        }

        return query;
    }

    @Override
    public void convertCreateTabletStatement(MySqlCreateTableStatement statement) {
        String tableName = statement.getTableName().replace("`", "").replace("\"", "");
        String comment = null;
        if (Objects.nonNull(statement.getComment())) {
            comment = statement.getComment().toString().replace("'", "").replace("\"", "");
        }
        List<SQLTableElement> tableElementList = statement.getTableElementList();
        List<SQLAssignItem> tableOptions = statement.getTableOptions();
        Map<String, Object> sqlAssignMap = new HashMap<>();
        tableOptions.forEach(option -> {
            sqlAssignMap.put(option.getTarget().toString(), option.getValue());
        });
        if (StringUtils.isNotBlank(comment)) {
            commentList.add("COMMENT ON TABLE " + tableName + " IS '" + comment + "';");
        }
        StringBuilder builder = new StringBuilder("CREATE TABLE ");
        builder.append(tableName).append("\n(");
        tableElementList.forEach(column -> {
            if (column instanceof SQLColumnDefinition) {
                SQLCharExpr commentSqlExpr = (SQLCharExpr) ((SQLColumnDefinition) column).getComment();
                String columnName = ((SQLColumnDefinition) column).getColumnName().replace("`", "").replace("\"", "");
                String columnComment = null;
                if (Objects.nonNull(commentSqlExpr)) {
                    columnComment = commentSqlExpr.getText();
                }
                String increment = null;
                if (sqlAssignMap.containsKey("AUTO_INCREMENT")) {
                    increment = sqlAssignMap.get("AUTO_INCREMENT").toString();
                }
                Boolean autoIncrement = ((SQLColumnDefinition) column).isAutoIncrement();
                if (autoIncrement) {
                    convertSequence(tableName, (SQLColumnDefinition) column, increment);
                }
                builder.append(buildColumn((SQLColumnDefinition) column, null));
                builder.append(",\n");
                if (StringUtils.isNotBlank(columnComment)) {
                    commentList.add("COMMENT ON COLUMN " + tableName + "." + columnName + " IS '" + columnComment + "';");
                }
            } else if (column instanceof MySqlPrimaryKey) {
                MySqlPrimaryKey mySqlPrimaryKey = (MySqlPrimaryKey) column;
                List<SQLSelectOrderByItem> sqlSelectOrderByItems = mySqlPrimaryKey.getIndexDefinition().getColumns();
                SQLIdentifierExpr expr = (SQLIdentifierExpr) sqlSelectOrderByItems.get(0).getExpr();
                String primaryName = tableName + "_pkey";
                alterList.add("ALTER TABLE " + tableName + " ADD CONSTRAINT " + primaryName + " PRIMARY KEY(" + expr.getName().replace("`", "") + ");");
            } else if (column instanceof MySqlUnique) {
                MySqlUnique mySqlKey = (MySqlUnique) column;
                SQLIndexDefinition sqlIndexDefinition = mySqlKey.getIndexDefinition();
                List<SQLSelectOrderByItem> sqlSelectOrderByItems = sqlIndexDefinition.getColumns();
                List<String> keys = new ArrayList<>();
                sqlSelectOrderByItems.forEach(keyItem -> {
                    if (keyItem.getExpr() instanceof SQLIdentifierExpr) {
                        SQLIdentifierExpr expr = (SQLIdentifierExpr) keyItem.getExpr();
                        keys.add(expr.getName().replace("`", ""));
                    } else if (keyItem.getExpr() instanceof SQLMethodInvokeExpr) {
                        SQLMethodInvokeExpr invokeExpr = (SQLMethodInvokeExpr) keyItem.getExpr();
                        keys.add(invokeExpr.getMethodName().replace("`", ""));
                    }
                });
                String indexName = "unique_" + tableName + "_" + StringUtils.join(keys, "_").replace("\"", "");
                alterList.add("ALTER TABLE " + tableName + " ADD CONSTRAINT " + indexName + " UNIQUE(" + StringUtils.join(keys, ",") + ");");
            } else if (column instanceof MySqlKey) {
                MySqlKey mySqlKey = (MySqlKey) column;
                SQLIndexDefinition sqlIndexDefinition = mySqlKey.getIndexDefinition();
                List<SQLSelectOrderByItem> sqlSelectOrderByItems = sqlIndexDefinition.getColumns();
                List<String> keys = new ArrayList<>();
                sqlSelectOrderByItems.forEach(keyItem -> {
                    if (keyItem.getExpr() instanceof SQLIdentifierExpr) {
                        SQLIdentifierExpr expr = (SQLIdentifierExpr) keyItem.getExpr();
                        keys.add(expr.getName().replace("`", ""));
                    } else if (keyItem.getExpr() instanceof SQLMethodInvokeExpr) {
                        SQLMethodInvokeExpr invokeExpr = (SQLMethodInvokeExpr) keyItem.getExpr();
                        keys.add(invokeExpr.getMethodName().replace("`", ""));
                    }
                });
                String indexName = "idx_" + tableName + "_" + StringUtils.join(keys, "_").replace("\"", "");
                indexList.add("CREATE INDEX " + indexName + " ON " + tableName + " USING btree (" + StringUtils.join(keys, ",") + ") TABLESPACE pg_default;");
            }
        });
        builder.replace(builder.length() - 2, builder.length() - 1, "");
        builder.append(")\n" + "WITH (orientation=row, compression=no);");
        creatTableList.add(builder.toString());
    }

    private void convertSequence(String tableName, SQLColumnDefinition column, String increment) {
        StringBuilder builder = new StringBuilder("SET search_path = ");
        builder.append(schemaName).append(";\n");
        String columnName = column.getColumnName().replace("`", "").replace("\"", "");
        sequenceName = tableName + "_" + columnName + "_seq";
        builder.append(" CREATE  SEQUENCE ").append(sequenceName).append("\n");
        builder.append(" START  WITH  ");
        if (StringUtils.isNotBlank(increment)) {
            builder.append(increment).append("\n");
        } else {
            builder.append("1\n");
        }
        builder.append(" INCREMENT  BY  1\n" + " NO MINVALUE  \n" + " MAXVALUE 9223372036854775807\n" + " CACHE 1;");

        sequenceList.add(builder.toString());
        StringBuilder alterSequence = new StringBuilder("ALTER SEQUENCE ");
        alterSequence.append(sequenceName).append(" OWNED BY ");
        alterSequence.append(tableName).append(".").append(columnName).append(";");
        alterSequenceList.add(alterSequence.toString());
    }

    @Override
    public void convertInsertStatement(MySqlInsertStatement statement) {
        String sql = statement.toString().replace("`", "").replace("CURRENT_TIMESTAMP()", "CURRENT_TIMESTAMP").replace("current_timestamp()", "CURRENT_TIMESTAMP").replace("b'0'", "0").replace("b'1'", "1");
        insertList.add(sql);
    }

    @Override
    public void convertCreateIndexStatement(SQLCreateIndexStatement statement) {
        String tableName = statement.getTableName().toString().replace("`", "");
        SQLIndexDefinition sqlIndexDefinition = statement.getIndexDefinition();
        List<SQLSelectOrderByItem> sqlSelectOrderByItems = sqlIndexDefinition.getColumns();
        String type = null;
        if (StringUtils.isNotBlank(sqlIndexDefinition.getType())) {
            type = sqlIndexDefinition.getType().toUpperCase();
        }
        List<String> keys = new ArrayList<>();
        sqlSelectOrderByItems.forEach(keyItem -> {
            if (keyItem.getExpr() instanceof SQLIdentifierExpr) {
                SQLIdentifierExpr expr = (SQLIdentifierExpr) keyItem.getExpr();
                keys.add(expr.getName().replace("`", ""));
            } else if (keyItem.getExpr() instanceof SQLMethodInvokeExpr) {
                SQLMethodInvokeExpr invokeExpr = (SQLMethodInvokeExpr) keyItem.getExpr();
                keys.add(invokeExpr.getMethodName().replace("`", ""));
            }
        });
        if (StringUtils.isNotBlank(type) && "UNIQUE".equals(type)) {
            String indexName = "unique_" + tableName + "_" + StringUtils.join(keys, "_").replace("\"", "");
            indexList.add("ALTER TABLE " + tableName + " ADD CONSTRAINT " + indexName + " UNIQUE(" + StringUtils.join(keys, ",") + ");");
        } else {
            String indexName = "idx_" + tableName + "_" + StringUtils.join(keys, "_").replace("\"", "");
            indexList.add("CREATE INDEX " + indexName + " ON " + tableName + " USING btree (" + StringUtils.join(keys, ",") + ") TABLESPACE pg_default;");
        }
    }

    @Override
    public void convertUpdateStatement(MySqlUpdateStatement statement) {
        String sql = statement.toString().replace("`", "").replace("CURRENT_TIMESTAMP()", "CURRENT_TIMESTAMP").replace("current_timestamp()", "CURRENT_TIMESTAMP").replace("b'0'", "0").replace("b'1'", "1");
        updateList.add(sql);
    }

    @Override
    public void convertDeleteStatement(MySqlDeleteStatement statement) {
        String sql = statement.toString().replace("`", "");
        updateList.add(sql);
    }

    @Override
    public void convertAlterTableStatement(SQLAlterTableStatement statement) {
        String tableName = statement.getTableName().toString().replace("`", "");
        statement.getItems().forEach(sqlAlterTableItem -> {
            if (sqlAlterTableItem instanceof SQLAlterTableAddColumn) {
                SQLAlterTableAddColumn addColumn = (SQLAlterTableAddColumn) sqlAlterTableItem;
                addColumn.getColumns().forEach(addColumnItem -> {
                    StringBuilder builder = new StringBuilder("ALTER TABLE ");
                    builder.append(tableName).append("");
                    builder.append(" ADD COLUMN ").append(buildColumn(addColumnItem, null)).append(";");
                    SQLCharExpr commentSqlExpr = (SQLCharExpr) addColumnItem.getComment();
                    String columnName = addColumnItem.getColumnName().replace("`", "").replace("\"", "");
                    ;
                    String columnComment = null;
                    if (Objects.nonNull(commentSqlExpr)) {
                        columnComment = commentSqlExpr.getText();
                    }
                    if (StringUtils.isNotBlank(columnComment)) {
                        commentList.add("COMMENT ON COLUMN " + tableName + "." + columnName + " IS '" + columnComment + "';");
                    }
                    alterList.add(builder.toString());
                });
            } else if (sqlAlterTableItem instanceof MySqlAlterTableModifyColumn) {
                MySqlAlterTableModifyColumn alterColumn = (MySqlAlterTableModifyColumn) sqlAlterTableItem;
                SQLColumnDefinition definition = alterColumn.getNewColumnDefinition();
                String columnName = definition.getColumnName().replace("`", "").replace("\"", "");
                StringBuilder dropBuilder = new StringBuilder("ALTER TABLE ");
                dropBuilder.append(tableName);
                dropBuilder.append(" ALTER ").append(columnName).append(" DROP NOT NULL,\n");
                dropBuilder.append(" ALTER ").append(columnName).append(" DROP DEFAULT;");
                alterList.add(dropBuilder.toString());

                StringBuilder builder = new StringBuilder("ALTER TABLE ");
                builder.append(tableName);
                builder.append(" ALTER ").append(modifyColumn(definition)).append(",\n");

                SQLDataTypeImpl dataType = (SQLDataTypeImpl) definition.getDataType();
                String dataTypeName = dataType.getName().toLowerCase();
                List<SQLColumnConstraint> constraints = definition.getConstraints();
                SQLExpr sqlExpr = definition.getDefaultExpr();
                if (Objects.nonNull(sqlExpr)) {
                    if (sqlExpr instanceof SQLIntegerExpr) {
                        SQLIntegerExpr sqlIntegerExpr = (SQLIntegerExpr) sqlExpr;
                        builder.append(" ALTER ").append(columnName).append(" SET DEFAULT ").append("'").append(sqlIntegerExpr.getValue()).append("'").append(",\n");
                    } else if (sqlExpr instanceof SQLBinaryExpr) {
                        SQLBinaryExpr sqlBinaryExpr = (SQLBinaryExpr) sqlExpr;
                        builder.append(" ALTER ").append(columnName).append(" SET DEFAULT ").append("'").append(sqlBinaryExpr.getValue()).append("'").append(",\n");
                    } else if (sqlExpr instanceof SQLCurrentTimeExpr) {
                        builder.append(" ALTER ").append(columnName).append(" SET DEFAULT ").append("pg_systimestamp()").append(",\n");
                    } else if (sqlExpr instanceof SQLCharExpr) {
                        SQLCharExpr sqlCharExpr = (SQLCharExpr) sqlExpr;
                        if ("varchar".equals(dataTypeName)) {
                            builder.append(" ALTER ").append(columnName).append(" SET DEFAULT ").append("'").append(sqlCharExpr.getText()).append("'::character varying").append(",\n");
                        } else {
                            builder.append(" ALTER ").append(columnName).append(" SET DEFAULT ").append(sqlCharExpr.getText()).append(",\n");
                        }
                    }
                }
                if (Objects.nonNull(constraints) && !constraints.isEmpty() && constraints.get(0) instanceof SQLNotNullConstraint) {
                    builder.append(" ALTER ").append(columnName).append(" SET NOT NULL,\n");
                }
                builder.replace(builder.length() - 2, builder.length() - 1, ";");
                alterList.add(builder.toString());
                SQLCharExpr commentSqlExpr = (SQLCharExpr) definition.getComment();
                String columnComment = null;
                if (Objects.nonNull(commentSqlExpr)) {
                    columnComment = commentSqlExpr.getText();
                }
                if (StringUtils.isNotBlank(columnComment)) {
                    commentList.add("COMMENT ON COLUMN " + tableName + "." + columnName + " IS '" + columnComment + "';");
                }
            } else if (sqlAlterTableItem instanceof SQLAlterTableAddIndex) {
                StringBuilder builder = new StringBuilder();
                SQLAlterTableAddIndex addIndex = (SQLAlterTableAddIndex) sqlAlterTableItem;
                SQLIndexDefinition sqlIndexDefinition = addIndex.getIndexDefinition();
                List<SQLSelectOrderByItem> sqlSelectOrderByItems = sqlIndexDefinition.getColumns();
                List<String> keys = new ArrayList<>();
                sqlSelectOrderByItems.forEach(keyItem -> {
                    if (keyItem.getExpr() instanceof SQLIdentifierExpr) {
                        SQLIdentifierExpr expr = (SQLIdentifierExpr) keyItem.getExpr();
                        keys.add(expr.getName().replace("`", ""));
                    } else if (keyItem.getExpr() instanceof SQLMethodInvokeExpr) {
                        SQLMethodInvokeExpr invokeExpr = (SQLMethodInvokeExpr) keyItem.getExpr();
                        keys.add(invokeExpr.getMethodName().replace("`", ""));
                    }
                });
                builder.append("CREATE INDEX " + sqlIndexDefinition.getName().toString().replace("`", "") + " ON " + tableName + "(" + StringUtils.join(keys, ",") + ");");
                alterList.add(builder.toString());
            } else if (sqlAlterTableItem instanceof SQLAlterTableAddConstraint) {
                SQLAlterTableAddConstraint addIndex = (SQLAlterTableAddConstraint) sqlAlterTableItem;
                SQLConstraint sqlConstraint = addIndex.getConstraint();
                if (sqlConstraint instanceof MySqlPrimaryKey) {
                    MySqlPrimaryKey mySqlPrimaryKey = (MySqlPrimaryKey) sqlConstraint;
                    List<SQLSelectOrderByItem> sqlSelectOrderByItems = mySqlPrimaryKey.getIndexDefinition().getColumns();
                    SQLIdentifierExpr expr = (SQLIdentifierExpr) sqlSelectOrderByItems.get(0).getExpr();
                    String primaryName = tableName + "_pkey";
                    alterList.add("ALTER TABLE " + tableName + " ADD CONSTRAINT " + primaryName + " PRIMARY KEY(" + expr.getName().replace("`", "") + ");");
                } else if (sqlConstraint instanceof MySqlUnique) {
                    MySqlUnique mySqlKey = (MySqlUnique) sqlConstraint;
                    SQLIndexDefinition sqlIndexDefinition = mySqlKey.getIndexDefinition();
                    List<SQLSelectOrderByItem> sqlSelectOrderByItems = sqlIndexDefinition.getColumns();
                    List<String> keys = new ArrayList<>();
                    sqlSelectOrderByItems.forEach(keyItem -> {
                        if (keyItem.getExpr() instanceof SQLIdentifierExpr) {
                            SQLIdentifierExpr expr = (SQLIdentifierExpr) keyItem.getExpr();
                            keys.add(expr.getName().replace("`", ""));
                        } else if (keyItem.getExpr() instanceof SQLMethodInvokeExpr) {
                            SQLMethodInvokeExpr invokeExpr = (SQLMethodInvokeExpr) keyItem.getExpr();
                            keys.add(invokeExpr.getMethodName().replace("`", ""));
                        }
                    });
                    String indexName = "unique_" + tableName + "_" + StringUtils.join(keys, "_").replace("\"", "");
                    alterList.add("ALTER TABLE " + tableName + " ADD CONSTRAINT " + indexName + " UNIQUE(" + StringUtils.join(keys, ",") + ");");
                } else if (sqlConstraint instanceof MySqlKey) {
                    MySqlKey mySqlKey = (MySqlKey) sqlConstraint;
                    SQLIndexDefinition sqlIndexDefinition = mySqlKey.getIndexDefinition();
                    List<SQLSelectOrderByItem> sqlSelectOrderByItems = sqlIndexDefinition.getColumns();
                    List<String> keys = new ArrayList<>();
                    sqlSelectOrderByItems.forEach(keyItem -> {
                        if (keyItem.getExpr() instanceof SQLIdentifierExpr) {
                            SQLIdentifierExpr expr = (SQLIdentifierExpr) keyItem.getExpr();
                            keys.add(expr.getName().replace("`", ""));
                        } else if (keyItem.getExpr() instanceof SQLMethodInvokeExpr) {
                            SQLMethodInvokeExpr invokeExpr = (SQLMethodInvokeExpr) keyItem.getExpr();
                            keys.add(invokeExpr.getMethodName().replace("`", ""));
                        }
                    });
                    String indexName = "idx_" + tableName + "_" + StringUtils.join(keys, "_").replace("\"", "");
                    indexList.add("CREATE INDEX " + indexName + " ON " + tableName + " USING btree (" + StringUtils.join(keys, ",") + ") TABLESPACE pg_default;");
                }
            } else if (sqlAlterTableItem instanceof MySqlAlterTableChangeColumn) {
                String columnName = ((MySqlAlterTableChangeColumn) sqlAlterTableItem).getColumnName().getSimpleName().replace("`", "").replace("\"", "");
                SQLColumnDefinition newColumn = ((MySqlAlterTableChangeColumn) sqlAlterTableItem).getNewColumnDefinition();
                String newColumnName = newColumn.getColumnName().replace("`", "").replace("\"", "");

                alterList.add("ALTER TABLE ONLY " + tableName + " RENAME " + columnName + " TO " + newColumnName + ";");
                StringBuilder builder = new StringBuilder("ALTER TABLE ").append(tableName);
                builder.append(" MODIFY ").append(buildColumn(newColumn, null)).append(";");
                alterList.add(builder.toString());

                SQLCharExpr commentSqlExpr = (SQLCharExpr) newColumn.getComment();
                String columnComment = null;
                if (Objects.nonNull(commentSqlExpr)) {
                    columnComment = commentSqlExpr.getText();
                }
                if (StringUtils.isNotBlank(columnComment)) {
                    commentList.add("COMMENT ON COLUMN " + tableName + "." + newColumnName + " IS '" + columnComment + "';");
                }
            }
        });
    }

    @Override
    public void convertRenameTableStatement(MySqlRenameTableStatement statement) {
        MySqlRenameTableStatement.Item item = (MySqlRenameTableStatement.Item) statement.getItems().get(0);
        String name = item.getName().getSimpleName().replace("`", "");
        String toName = item.getTo().getSimpleName().replace("`", "");
        alterList.add("ALTER TABLE " + name + " RENAME TO " + toName + ";");
    }

    @Override
    public void convertDropTableStatement(SQLDropTableStatement statement) {
        String table = ((SQLIdentifierExpr) statement.getTableSources().get(0).getExpr()).getName().replace("`", "");
        alterList.add("DROP TABLE " + table + ";");
    }


    @Override
    public void convertTruncateStatement(SQLTruncateStatement statement) {
        // TODO
    }

    private String buildColumn(SQLColumnDefinition definition, String increment) {
        StringBuilder builder = new StringBuilder("");
        Boolean autoIncrement = definition.isAutoIncrement();
        String columnName = definition.getColumnName().replace("`", "").replace("\"", "");
        SQLDataTypeImpl dataType = (SQLDataTypeImpl) definition.getDataType();
        String dataTypeName = dataType.getName().toLowerCase();
        List<SQLExpr> sqlExprs = dataType.getArguments();
        List<SQLColumnConstraint> constraints = definition.getConstraints();
        if ("varchar".equals(dataTypeName)) {
            SQLIntegerExpr sqlIntegerExpr = (SQLIntegerExpr) sqlExprs.get(0);
            builder.append(columnName).append(" ").append("character varying(").append(sqlIntegerExpr.getNumber()).append(")");
        } else if ("timestamp".equals(dataTypeName) || "datetime".equals(dataTypeName)) {
            builder.append(columnName).append(" ").append("timestamp(0) without time zone ");
        } else if ("int".equals(dataTypeName)) {
            builder.append(columnName).append(" ").append("integer ");
        } else if ("decimal".equals(dataTypeName)) {
            SQLIntegerExpr precision = (SQLIntegerExpr) sqlExprs.get(0);
            SQLIntegerExpr scale = (SQLIntegerExpr) sqlExprs.get(1);
            builder.append(columnName).append(" TYPE decimal(").append(precision.getNumber()).append(", ").append(scale.getNumber()).append(")");
        } else {
            builder.append(columnName).append(" ").append(dataTypeName);
        }
        SQLExpr sqlExpr = definition.getDefaultExpr();
        if (autoIncrement) {
            builder.append(" DEFAULT ").append("nextval('").append(sequenceName).append("'::regclass)");
        } else if (Objects.nonNull(sqlExpr)) {
            if (sqlExpr instanceof SQLIntegerExpr) {
                SQLIntegerExpr sqlIntegerExpr = (SQLIntegerExpr) sqlExpr;
                builder.append(" DEFAULT ").append("'").append(sqlIntegerExpr.getValue()).append("'");
            } else if (sqlExpr instanceof SQLBinaryExpr) {
                SQLBinaryExpr sqlBinaryExpr = (SQLBinaryExpr) sqlExpr;
                builder.append(" DEFAULT ").append("'").append(sqlBinaryExpr.getValue()).append("'");
            } else if (sqlExpr instanceof SQLCurrentTimeExpr) {
                builder.append(" DEFAULT ").append("pg_systimestamp()");
            } else if (sqlExpr instanceof SQLCharExpr) {
                SQLCharExpr sqlCharExpr = (SQLCharExpr) sqlExpr;
                if ("varchar".equals(dataTypeName)) {
                    builder.append(" DEFAULT ").append("'").append(sqlCharExpr.getText()).append("'::character varying");
                } else {
                    builder.append(" DEFAULT ").append(sqlCharExpr.getText());
                }
            }
        }
        if (Objects.nonNull(constraints) && !constraints.isEmpty() && constraints.get(0) instanceof SQLNotNullConstraint) {
            builder.append(" NOT NULL");
        }
        return builder.toString();
    }

    private String modifyColumn(SQLColumnDefinition definition) {
        StringBuilder builder = new StringBuilder("");
        String columnName = definition.getColumnName().replace("`", "").replace("\"", "");
        SQLDataTypeImpl dataType = (SQLDataTypeImpl) definition.getDataType();
        String dataTypeName = dataType.getName().toLowerCase();
        List<SQLExpr> sqlExprs = dataType.getArguments();
        if ("varchar".equals(dataTypeName)) {
            SQLIntegerExpr sqlIntegerExpr = (SQLIntegerExpr) sqlExprs.get(0);
            builder.append(columnName).append(" ").append("character varying(").append(sqlIntegerExpr.getNumber()).append(")");
        } else if ("timestamp".equals(dataTypeName) || "datetime".equals(dataTypeName)) {
            builder.append(columnName).append(" ").append("timestamp(0) without time zone ");
        } else if ("int".equals(dataTypeName)) {
            builder.append(columnName).append(" ").append("integer ");
        } else if ("decimal".equals(dataTypeName)) {
            SQLIntegerExpr precision = (SQLIntegerExpr) sqlExprs.get(0);
            SQLIntegerExpr scale = (SQLIntegerExpr) sqlExprs.get(1);
            builder.append(columnName).append(" TYPE decimal(").append(precision.getNumber()).append(", ").append(scale.getNumber()).append(")");
        } else {
            builder.append(columnName).append(" ").append(dataTypeName);
        }
        return builder.toString();
    }
}
